use "$path\Intermediary Data\localshock_dataforReg_v2.dta",clear

drop if prix_ht==.
drop  j j3
xtset id daten
gen d_dieselrott=ln(diesel_rotterdam_euro)-ln(l1.diesel_rotterdam_euro)
drop diesel_rotterdam_euro dprix1 

drop d_dieselr
gen d_dieselr=d_dieselrott

merge m:1 id_pdv using "$path\Intermediary Data\data_PDV_autoroutes_supermarches_v3_withlonglat_mergecodeinsee.dta"
keep if _merge!=2 
drop _merge

merge m:m id using  "$path\Intermediary Data\base_id_suff_stat_6Y"
keep if _m==3
drop _m

merge m:m id using  "$path\Intermediary Data\base_base_suff_stat_6Y_forinteractkurtfreq"
keep if _m==3
drop _m



gen prix_str=string(prix)
gen length_prix=length(prix_str)
capture drop last_digit
gen last_digit=substr(prix_str, length_prix,1)
destring last_digit, replace force
drop prix_str
drop length_prix


bysort id (daten): gen change_price=1 if prix[_n]!=prix[_n+1] 
capture drop psychological_prices
capture drop sum_psychological_prices
capture drop sum_change_price
capture drop mean_psychological_prices
 
gen psychological_prices=0 if change_price==1
replace psychological_prices=1 if change_price==1 & (last_digit==0 | last_digit==9)
bysort id (daten): egen sum_psychological_prices=sum(psychological_prices)
bysort id (daten): egen sum_change_price=sum(change_price)
gen mean_psychological_prices=sum_psychological_prices/sum_change_price

bysort id (daten): egen mean_prix=mean(prix)



bysort id: gen first_obs=1 if _n==1
keep if first_obs==1

*** Merge with UU
merge m:1 codeinsee using "$path\Intermediary Data\UU2010_au_01-01-2020.dta"
keep if _merge!=2 
drop _merge

*** Merge with population
merge m:1 codeinsee using "$path\Intermediary Data\BTX_TD_POP1A_2016.dta"
keep if _merge!=2
drop _merge


gen supermarche=0 if classif_num!=. 
replace supermarche=1 if (classif_num==1 | classif_num==2)

gen independant=0 if classif_num!=. 
replace independant=1 if classif_num==0

gen compagnie_distrib_petrol=0 if classif_num!=. 
replace compagnie_distrib_petrol=1 if (classif_num==3 | classif_num==4)




*** Rural Urban ***
gen rural=type_com=="RURAL"
gen ville_centre=statut_2017=="C"
gen ville_banlieue=statut_2017=="B"
gen ville_isolee=statut_2017=="I"
gen ville_rurale=statut_2017=="R"

*** Pop
sum pop, d
capture drop pop_class
gen pop_class=.
replace pop_class=0 if pop<1700
replace pop_class=1 if pop>=1700 & pop<4800
replace pop_class=2 if pop>=4800 & pop<13700
replace pop_class=3 if pop>=13700 & pop!=.

*** Dep
encode dep, gen(num_dep)

*** Psychological prices
capture drop psycho
sum mean_psychological_prices,d
gen psycho=(mean_psychological_prices>.3106501)+(mean_psychological_prices>.4280595)+(mean_psychological_prices>.5801146)
replace psycho=. if mean_psychological_prices==.

*** Mean prices
capture drop price_lev
sum mean_prix,d
gen price_lev=(mean_prix>1203.365)+(mean_prix>1217.009)+(mean_prix>1241.97)
replace price_lev=. if mean_prix==.


gen kur_freq=kur/freq

**** Format Table A5 ***

capture drop col1
capture drop col2
capture drop col3
capture drop Panel
capture drop Var

*** Col1: Kur/Freq
reg kur_freq i.supermarche  ville_centre ville_banlieue ville_isolee ville_rurale ib3.pop_class ib3.psycho ib3.price_lev, absorb(num_dep) robust

gen col1=.
replace col1=_b[1.supermarche] if _n==1
replace col1=_se[1.supermarche] if _n==2
replace col1=. if _n==3

replace col1=_b[0.psycho] if _n==4
replace col1=_se[0.psycho] if _n==5
replace col1=_b[1.psycho] if _n==6
replace col1=_se[1.psycho] if _n==7
replace col1=_b[2.psycho] if _n==8
replace col1=_se[2.psycho] if _n==9
replace col1=. if _n==10

replace col1=_b[ville_centre] if _n==11
replace col1=_se[ville_centre] if _n==12
replace col1=_b[ville_banlieue] if _n==13
replace col1=_se[ville_banlieue] if _n==14
replace col1=_b[ville_isolee] if _n==15
replace col1=_se[ville_isolee] if _n==16
replace col1=. if _n==17

replace col1=_b[0.pop_class] if _n==18
replace col1=_se[0.pop_class] if _n==19
replace col1=_b[1.pop_class] if _n==20
replace col1=_se[1.pop_class] if _n==21
replace col1=_b[2.pop_class] if _n==22
replace col1=_se[2.pop_class] if _n==23
replace col1=. if _n==24

replace col1=_b[0.price_lev] if _n==25
replace col1=_se[0.price_lev] if _n==26
replace col1=_b[1.price_lev] if _n==27
replace col1=_se[1.price_lev] if _n==28
replace col1=_b[2.price_lev] if _n==29
replace col1=_se[2.price_lev] if _n==30
replace col1=. if _n==31

replace col1=_b[_cons] if _n==32
replace col1=_se[_cons] if _n==33

replace col1=e(r2) if _n==34
replace col1=e(N) if _n==35


*** Col2: Kurtosis
reg kurtosis i.supermarche ville_centre ville_banlieue ville_isolee ville_rurale ib3.pop_class ib3.psycho ib3.price_lev, absorb(num_dep) robust
gen col2=.
replace col2=_b[1.supermarche] if _n==1
replace col2=_se[1.supermarche] if _n==2
replace col2=. if _n==3

replace col2=_b[0.psycho] if _n==4
replace col2=_se[0.psycho] if _n==5
replace col2=_b[1.psycho] if _n==6
replace col2=_se[1.psycho] if _n==7
replace col2=_b[2.psycho] if _n==8
replace col2=_se[2.psycho] if _n==9
replace col2=. if _n==10

replace col2=_b[ville_centre] if _n==11
replace col2=_se[ville_centre] if _n==12
replace col2=_b[ville_banlieue] if _n==13
replace col2=_se[ville_banlieue] if _n==14
replace col2=_b[ville_isolee] if _n==15
replace col2=_se[ville_isolee] if _n==16
replace col2=. if _n==17

replace col2=_b[0.pop_class] if _n==18
replace col2=_se[0.pop_class] if _n==19
replace col2=_b[1.pop_class] if _n==20
replace col2=_se[1.pop_class] if _n==21
replace col2=_b[2.pop_class] if _n==22
replace col2=_se[2.pop_class] if _n==23
replace col2=. if _n==24

replace col2=_b[0.price_lev] if _n==25
replace col2=_se[0.price_lev] if _n==26
replace col2=_b[1.price_lev] if _n==27
replace col2=_se[1.price_lev] if _n==28
replace col2=_b[2.price_lev] if _n==29
replace col2=_se[2.price_lev] if _n==30
replace col2=. if _n==31

replace col2=_b[_cons] if _n==32
replace col2=_se[_cons] if _n==33

replace col2=e(r2) if _n==34
replace col2=e(N) if _n==35


*** Col3: Frequency

reg freq i.supermarche   ville_centre ville_banlieue ville_isolee ville_rurale ib3.pop_class ib3.psycho ib3.price_lev, absorb(num_dep) robust

gen col3=.
replace col3=_b[1.supermarche] if _n==1
replace col3=_se[1.supermarche] if _n==2
replace col3=. if _n==3

replace col3=_b[0.psycho] if _n==4
replace col3=_se[0.psycho] if _n==5
replace col3=_b[1.psycho] if _n==6
replace col3=_se[1.psycho] if _n==7
replace col3=_b[2.psycho] if _n==8
replace col3=_se[2.psycho] if _n==9
replace col3=. if _n==10

replace col3=_b[ville_centre] if _n==11
replace col3=_se[ville_centre] if _n==12
replace col3=_b[ville_banlieue] if _n==13
replace col3=_se[ville_banlieue] if _n==14
replace col3=_b[ville_isolee] if _n==15
replace col3=_se[ville_isolee] if _n==16
replace col3=. if _n==17

replace col3=_b[0.pop_class] if _n==18
replace col3=_se[0.pop_class] if _n==19
replace col3=_b[1.pop_class] if _n==20
replace col3=_se[1.pop_class] if _n==21
replace col3=_b[2.pop_class] if _n==22
replace col3=_se[2.pop_class] if _n==23
replace col3=. if _n==24

replace col3=_b[0.price_lev] if _n==25
replace col3=_se[0.price_lev] if _n==26
replace col3=_b[1.price_lev] if _n==27
replace col3=_se[1.price_lev] if _n==28
replace col3=_b[2.price_lev] if _n==29
replace col3=_se[2.price_lev] if _n==30
replace col3=. if _n==31

replace col3=_b[_cons] if _n==32
replace col3=_se[_cons] if _n==33

replace col3=e(r2) if _n==34
replace col3=e(N) if _n==35


gen Panel="Type of gas station" if _n==1
replace Panel="% of prices ending in (0,9)" if _n==4
replace Panel="Location" if _n==11
replace Panel="Population" if _n==18
replace Panel="Price level" if _n==25
replace Panel="Intercept" if _n==32
replace Panel="R2" if _n==34
replace Panel="Nobs" if _n==35

gen Var="Supermarket" if _n==1
replace Var="Other gas stations" if _n==3
replace Var="< 1st quartile" if _n==4
replace Var="< median and >1st quartile" if _n==6
replace Var="< 3rd quartile and > median" if _n==8
replace Var="> 3rd quartile" if _n==10
replace Var="City Center" if _n==11
replace Var="Suburban Area" if _n==13
replace Var="Isolated City" if _n==15
replace Var="Rural Area" if _n==17
replace Var="< 1st quartile" if _n==18
replace Var="< median and >1st quartile" if _n==20
replace Var="< 3rd quartile and > median" if _n==22
replace Var="> 3rd quartile" if _n==24
replace Var="< 1st quartile" if _n==25
replace Var="< median and >1st quartile" if _n==27
replace Var="< 3rd quartile and > median" if _n==29
replace Var="> 3rd quartile" if _n==31




keep if _n<=35
keep col1 col2 col3 Var Panel
order Panel Var col1 col2 col3


gen tstatcol1=.
gen tstatcol2=.
gen tstatcol3=.



foreach i in 1 4 6 8 11 13 15 18 20 22 25 27 29 32{
replace tstatcol1=col1[`i']/col1[`i'+1] if _n==`i'
replace tstatcol2=col2[`i']/col2[`i'+1] if _n==`i'
replace tstatcol3=col3[`i']/col3[`i'+1] if _n==`i'
}

gen starscol1=""
gen starscol2=""
gen starscol3=""


foreach i in 1 2 3 {
replace starscol`i'="***" if abs(tstatcol`i')>=2.58 & tstatcol`i'!=.
replace starscol`i'="**" if abs(tstatcol`i')<2.58 & abs(tstatcol`i')>=1.96 & tstatcol`i'!=.
replace starscol`i'="*" if abs(tstatcol`i')<1.96 & abs(tstatcol`i')>=1.64 & tstatcol`i'!=.
}

drop tstat*
rename col* coltemp*

foreach i in 1 2 3 {
replace coltemp`i'=round(coltemp`i', 0.001)
tostring coltemp`i', gen(coltempbis`i') force format("%9.3f")  
egen col`i'=concat(coltempbis`i' starscol`i')
}

rename col1 Ratio
rename col2 Kurtosis
rename col3 Frequency


replace Ratio="Ref" if Ratio=="."
replace Kurtosis="Ref" if Kurtosis=="."
replace Frequency="Ref" if Frequency=="."

keep Panel Var  Ratio Kurtosis Frequency 

save "$path\Graphs_Tables\TableA5_Edited.dta", replace




